# -*- coding: utf-8 -*-
# author:HHR
# time:2021/1/14:18:10
# ___msg___ = '年费'
import sys

from selenium.webdriver.support import expected_conditions as EC
from selenium.webdriver.support.ui import WebDriverWait
from quote_folder.Db_connect import DB_connect
from selenium.webdriver.common.by import By
from selenium import webdriver
from bs4 import BeautifulSoup
import time, random, re

from 查询并更新系统专利状态 import is_search_ok


class annual_msg():
    def __init__(self):
        self.get_url = "http://cpquery.cnipa.gov.cn/"
        self.develop_con = DB_connect().online_db()  # 开发数据库连接
        self.develop_cur = self.develop_con.cursor()
        self.bigdata_con = DB_connect().ccy_db()  # 数据组数据库连接
        self.bigdata_cur = self.bigdata_con.cursor()
        self.redis_con = DB_connect().patents_reids()  # redis连接

    def login_gov(self):
        profile = webdriver.FirefoxOptions()
        profile.set_preference('network.proxy.type', 1)
        browser = webdriver.Firefox(options=profile, executable_path=r'D:\工具\geckodriver-v0.15.0-win64\geckodriver.exe')
        browser.get(self.get_url)
        time.sleep(5)
        WebDriverWait(browser, 10).until(EC.presence_of_element_located((By.ID, "username"))).clear()
        # browser.find_element_by_id('username').send_keys('CN00675769')
        # browser.find_element_by_id('username').send_keys('42264')
        browser.find_element_by_id('username1').send_keys('18071440149')
        time.sleep(1)
        browser.find_element_by_id('password').clear()
        # browser.find_element_by_id('password').send_keys('Gv89NWWY@')
        # browser.find_element_by_id('password').send_keys('Xhhg0613@@')
        browser.find_element_by_id('password1').send_keys('Xhhg123456!')
        is_search_ok(browser)
        current_url = browser.current_url
        return browser, current_url

    # 2020207090033
    username_info_list = [
        {"username": "15027399434", "password": "YJyj!1234"},
        {"username": "18071440149", "password": "Xhhg123456!"},
        {"username": "15978554102", "password": "Hhr31415926!"},
        {"username": "17362997328", "password": "Ccy1994!"},
        {"username": "13283766953", "password": "Ws1234567890~"},
        {"username": "13296529358", "password": "king123!B"},
        {"username": "18672982996", "password": "Ws1296817327~"},
        {"username": "17634417772", "password": "Ws123456cyh~"},
        {"username": "15039703722", "password": "WSws123456~"},
        {"username": "17710225394", "password": "Wq123456~"},
        {"username": "15318998458", "password": "123456Ws~"},
        {"username": '15938630689', 'password': "Whr123456~"},
        {"username": '18733151070', 'password': "Jx123456~"},
        {"username": '13995565120', 'password': "QWEqwe123~"},
        {"username": '15609508281', 'password': "Zq418317382@"},
        {"username": '15039738897', 'password': "Bl123456~"},
        {"username": '18240721210', 'password': "Zx123456~"},
        {"username": '15571590720', 'password': "Xj152588*"},
        {"username": '17630617464', 'password': "Zys123456~"},
        {"username": '17754433386', 'password': "Fj123456~"},
    ]

    def execute_sql(self, insert_sql, sqh):
        """ 执行sql插入 """
        try:
            self.bigdata_cur.execute(insert_sql)
        except Exception as E:
            print(insert_sql)
            self.redis_con.sadd("外部接口接口查询", sqh)
            print("插入错误：", E)

    def get_payment_information(self, soup, sqh, now_time):
        """ 获取缴费信息 """
        jf_div_list = soup.select("div[id='djfid'] table tr")  # 未缴费
        jf_list = []
        if len(jf_div_list) > 1:
            for jf_tsg in jf_div_list[1:]:
                jf_item = {}
                jf_td_list = jf_tsg.select("td > span")
                jf_item["cost_category"] = jf_td_list[0]["title"]  # 费用种类
                jf_item["pay_amount"] = jf_td_list[1]["title"]  # 应缴金额
                jf_item["close_date"] = jf_td_list[2]["title"]  # 缴费截止日
                jf_item["pay_status"] = jf_td_list[3]["title"]  # 费用状态
                insert_sql = f"INSERT INTO annual_fee_information_{now_time} (application_number, money_type, money, close_date, pay_status, created_at) " \
                             f"VALUES('{sqh}', '{jf_item['cost_category']}', '{jf_item['pay_amount']}', '{jf_item['close_date']}', '{jf_item['pay_status']}', NOW())"
                self.execute_sql(insert_sql, sqh)
                jf_list.append(jf_item)
        return jf_list

    def By_payment_information(self, soup, sqh, now_time):
        """ 获取以缴费信息 """
        yjf_div_list = soup.select(("div[id='yjfid'] table tr"))  # 已缴费
        yjf_list = []
        if len(yjf_div_list) > 1:
            for jf_tsg in yjf_div_list[1:]:
                yjf_item = {}
                jf_td_list = jf_tsg.select("td > span")
                yjf_item["cost_category"] = jf_td_list[0]["title"]  # 费用种类
                yjf_item["payment_amount"] = jf_td_list[1]["title"]  # 缴费金额
                yjf_item["pay_date"] = jf_td_list[2]["title"]  # 缴费日期
                yjf_item["name_payer"] = jf_td_list[3]["title"]  # 缴费人
                yjf_item["note_the_code"] = jf_td_list[4]["title"]  # 票据代码
                yjf_item["ticket_number"] = jf_td_list[5]["title"]  # 票据号码
                yjf_list.append(yjf_item)
                insert_sql = f"INSERT INTO by_payment_information_{now_time} (application_number, cost_category, payment_amount, pay_date, name_payer, note_the_code, ticket_number, create_time, updata_time) " \
                             f"VALUES('{sqh}', '{yjf_item['cost_category']}', '{yjf_item['payment_amount']}', '{yjf_item['pay_date']}', " \
                             f"'{yjf_item['name_payer']}', '{yjf_item['note_the_code']}', '{yjf_item['ticket_number']}', NOW(), NOW())"
                self.execute_sql(insert_sql, sqh)
        return yjf_list

    def overdue_fine(self, soup, sqh, now_time):
        """ 滞纳金信息 """
        overdue_div_list = soup.select(("div[id='znjid'] table tr"))  # 滞纳金
        overdue_list = []
        if len(overdue_div_list) > 1:
            for overdue_tsg in overdue_div_list[1:]:
                overdue_item = {}
                overdue_td_list = overdue_tsg.select("td > span")
                payment_time = overdue_td_list[0]["title"].replace("年", "-").replace("月", "-").replace("日", "-").split(
                    "到")  # 缴费时间
                overdue_item["payment_start_time"] = payment_time[0][:-1]
                overdue_item["payment_end_time"] = payment_time[1][:-1]
                overdue_item["current_annual_fee"] = overdue_td_list[1]["title"]  # 当前年费金额
                overdue_item["late_fee"] = overdue_td_list[2]["title"]  # 应交滞纳金额
                overdue_item["total"] = overdue_td_list[3]["title"]  # 总计
                overdue_list.append(overdue_item)
                insert_sql = f"INSERT INTO overdue_fine_{now_time} (application_number, payment_start_time, payment_end_time, current_annual_fee, late_fee, total, create_time, updata_time) " \
                             f"VALUES('{sqh}', '{overdue_item['payment_start_time']}', '{overdue_item['payment_end_time']}', '{overdue_item['current_annual_fee']}', '{overdue_item['late_fee']}', '{overdue_item['total']}', NOW(), NOW())"
                self.execute_sql(insert_sql, sqh)
        return overdue_list

    def get_impowerDay(self, soup, sqh):
        """获取授权日"""
        # applicationNumber = "CN" + sqh.strip()[:-1] + "." + sqh.strip()[-1]
        impower_day_list = soup.select("span[name='record_gkgg:gongkaigglx']")
        impday_list = soup.select("span[name='record_gkgg:gonggaor']")
        flag, impday = False, ''
        for item_num in range(len(impower_day_list)):
            if "授权" in impower_day_list[item_num]['title']:
                impday = impday_list[item_num]['title']
                flag = True

                # impday_sql = f"Update patent_table SET impower_day = '{impday}' where application_number='{sqh}'"
                # self.execute_sql(impday_sql, sqh)
        if flag:
            with open("授权日.txt", "w") as fr:
                fr.write(impday)
            print(impday)
        else:
            with open("授权日.txt", "w") as fr:
                fr.write(" ")

            print("未查到授权日！")

    def get_msg(self, sqh, browser, current_url, now_time):
        sqh = re.sub(r'ZL', '', sqh)
        pat = ''.join(re.findall(r'select-key:shenqingh=(.*?)&select-key:zhuanlilx', current_url))  # 专利授权日页面url
        # pat = ''.join(re.findall(r'select-key:shenqingh=(.*?)&select-key:backPage', current_url))  # 专利费用信息页面url
        url = re.sub(pat, sqh, current_url)
        browser.get(url)
        count_page = browser.page_source
        soup = BeautifulSoup(count_page, "lxml")
        if "应缴费信息" in count_page:
            print('-' * 50, "开始抓取未缴费信息", '-' * 50)
            jf_item = self.get_payment_information(soup, sqh, now_time)  # 获取缴费信息
            print(f"抓取到未缴费信息 {len(jf_item)} 条")
            print('-' * 50, "开始抓取以缴费信息", '-' * 50)
            yjf_item = self.By_payment_information(soup, sqh, now_time)  # 获取以缴费信息
            print(f"抓取到以缴费信息 {len(yjf_item)} 条")
            print('-' * 50, "开始抓取滞纳金信息", '-' * 50)
            overdue_item = self.overdue_fine(soup, sqh, now_time)  # 获取滞纳金信息
            print(f"抓取到滞纳金信息 {len(overdue_item)} 条")
            if len(jf_item) == 0 and len(yjf_item) == 0 and len(overdue_item) == 0:
                self.redis_con.sadd("未抓到申请号", sqh)

        elif '账号登录' in browser.page_source:
            print('\n', "账号被挤或异常掉线，已退出！")
            sys.exit()
        else:
            print("网站查询上限..............")
            print(f"网站查询上限，并将申请号：{sqh}插入redis库中")
            self.redis_con.sadd("外部接口接口查询", sqh)
            time.sleep(500)
        # self.get_impowerDay(soup, sqh)   									# 获取授权日

    def main_fun(self):
        now_time = time.strftime("%m%d", time.localtime())
        browser, current_url = self.login_gov()
        # sqh_li, number_li = self.get_sqh()

        need_time = 0
        # # for application_number in ["2020204600674", "2019210164608", "2019207178272", "2020216179031", "2019219412707", "2017208136009", "2020213729097", "2020204600602", "2016214809209", "2019204435994", "2019203058642", "2019204671060", "2019204690095", "2020216178927"]:
        while self.redis_con.scard("外部接口接口查询"):
            start_time = time.time()
            application_number = self.redis_con.spop("外部接口接口查询")  # 外部接口接口查询
            print('-' * 20,
                  f"开始抓取 {application_number} 的信息, 剩余 {self.redis_con.scard('外部接口接口查询')} 个, 大约 {need_time} 分钟",
                  '-' * 20)
            self.get_msg(application_number, browser, current_url, now_time)  # 获取专利缴费信息
            self.bigdata_con.commit()
            print()
            print()
            end_time = time.time()
            need_time = round((end_time - start_time) * DB_connect().patents_reids().scard("外部接口接口查询") / 60, 1)

        # with open("sqh.txt", "r") as fw:
        #     for i in fw:
        #         application_number = i.strip()
        #         # print(f"开始抓取 {application_number}，一共有{number_li}条申请号")

        #         self.get_msg(application_number, browser, current_url, now_time)  # 获取专利缴费信息
        #         self.bigdata_con.commit()

        self.bigdata_con.commit()
        self.bigdata_cur.close()
        self.bigdata_con.close()
        self.develop_cur.close()
        self.develop_con.close()
        browser.close()

    def get_sqh(self):
        sql = "SELECT application_number FROM patent_table WHERE impower_day IS NULL"
        self.bigdata_cur.execute(sql)
        sqh_li = self.bigdata_cur.fetchall()
        number_li = len(sqh_li)
        return sqh_li, number_li


if __name__ == '__main__':
    annual_msg().main_fun()

# 2020207090033
# 2019305893887
